<?php

defined('BASEPATH') or die('forbidden');

class Database_Controller Extends Controller {

    function _init() {
        check_level(50, 'general', 'database');
    }

    function index() {
        $tables = array();
        $tables_data_length = $tables_index_length = $tables_free_length = $tables_data_count = 0;
        $this->db->query("SHOW TABLE STATUS FROM {$this->db->name}");
        while ($row = $this->db->get_array('default', MYSQL_ASSOC)) {
            $tables[] = $row;
            $tables_data_length += $row['Data_length'];
            $tables_data_count += $row['Rows'];
            $tables_index_length += $row['Index_length'];
            $tables_free_length += $row['Data_free'];
        }

        $data['tables'] = $tables;
        $data['tables_data_length'] = $tables_data_length;
        $data['tables_data_count'] = $tables_data_count;
        $data['tables_index_length'] = $tables_index_length;
        $data['tables_free_length'] = $tables_free_length;

        $one = $this->db->one("SELECT * FROM {pre}config WHERE name='sql'");
        $saved_sql = array();
        if ($one && $one['content'])
            $saved_sql = explode('###', $one['content']);

        $data['saved_sql'] = array_values(array_filter($saved_sql));
        $this->view->display("general/database", $data);
    }

    function get_tables() {
        $tables = $list = array();
        $this->db->query("SHOW TABLES", 'tablelist');
        $list = $this->db->row('tablelist', MYSQL_BOTH);
        foreach ($list as $m => $n) {
            $subrow = $this->db->one("SELECT COUNT(*) AS dd FROM {$n[0]}");
            $nums = $subrow['dd'];
            $tables[$n[0]] = "{$n[0]}({$nums})";
        }
        return $tables;
    }

    function do_query() {
        $do_action = $this->input->post('do_action');
        if ($do_action == 'query') {
            check_level(50, 'general', 'database', 'execute');
        } else if (in_array($do_action, array('view_info', 'view_data'))) {
            check_level(50, 'general', 'database', 'browser');
        } else if (in_array($do_action, array('optimize', 'repair', 'optimize_all', 'repair_all'))) {
            check_level(50, 'general', 'database', 'repair');
        } else {
            check_level(50, 'general', 'database', 'index');
        }

        echo "<style type='text/css'>xmp,body{margin:0;padding:0;line-height:18px;font-size:12px;font-family:Verdana;background:#fefefe;}</style>";
        if ($do_action == '')
            error(translate('GENERAL_DATABASE_UNKNOW_ERROR'));
        $tablename = isset($_POST['tablename']) ? $_POST['tablename'] : array();
        if (in_array($do_action, array('query', 'optimize_all', 'repair_all'))) {
            $sqlquery = $this->input->post("sqlquery", "stripslashes");
            $areaIds = $this->input->post("areaIds");
            if ($sqlquery && $areaIds) {
                insert_log( translate('GENERAL_DATABASE_ASYNC_AREA_SQL',$sqlquery,implode(",", $areaIds)));
                exit( translate('GENERAL_DATABASE_EXCUTE_AREA_SQL_TIPS',$sqlquery));
            }
            $this->$do_action();
        } else if (count($tablename) == 0) {
            exit(translate('GENERAL_DATABASE_NO_TABLE_NAME'));
        } else {
            foreach ($tablename as $m => $n) {
                $this->table_name = $n;
                $this->$do_action();
            }
        }
    }

    function save_query() {
        check_level(50, 'general', 'database', 'repair');
        $sql = $this->input->post("sql");
        if (!$sql)
            exit(translate('GENERAL_DATABASE_NO_EMPTY'));
        $this->db->query("UPDATE {pre}config SET content=concat_ws('###', '{$sql}', content) WHERE name='sql'");
        echo translate('COMMON_VIEW_SAVE_SUCCESS');
    }

    function del_query() {
        check_level(50, 'general', 'database', 'repair');
        $index = $this->input->post("index");
        $one = $this->db->one("SELECT * FROM {pre}config WHERE name='sql'");
        if ($one && $one['content']) {
            $sql = array_values(array_filter(explode('###', $one['content'])));
            if (isset($sql[$index]))
                unset($sql[$index]);
            $content = mysql_real_escape_string(implode('###', $sql));
            $this->db->query("UPDATE {pre}config SET content='{$content}' WHERE name='sql'");
            echo translate("COMMON_VIEW_DEL_SUCCESS");
        }
    }

    function view_info() {
        check_level(50, 'general', 'database', 'browser');
        if ($this->table_name == '')
            exit(translate('GENERAL_DATABASE_NO_TABLE_NAME'));
        $this->db->query("SHOW CREATE TABLE {$this->table_name}", "view");
        $row = $this->db->get_array("view", MYSQL_BOTH);
        $info = $row[1];
        echo "<xmp>{$info};</xmp><br />";
    }

    function view_data() {
        check_level(50, 'general', 'database', 'browser');
        if ($this->table_name == '')
            exit(translate('GENERAL_DATABASE_NO_TABLE_NAME'));
        $sqlquery = "SELECT * FROM {$this->table_name}";
        $this->query($sqlquery);
    }

    function optimize() {
        check_level(50, 'general', 'database', 'repair');
        if ($this->table_name == '')
            exit(translate('GENERAL_DATABASE_NO_TABLE_NAME'));
        if ($this->db->simple("OPTIMIZE TABLE {$this->table_name}")) {
            echo translate('GENERAL_DATABASE_OPTIMIZE_SUCCESS',$this->table_name);
        } else {
                        echo translate('GENERAL_DATABASE_OPTIMIZE_FAIL',$this->table_name);
        }
    }

    function optimize_all() {
        check_level(50, 'general', 'database', 'repair');
        $this->db->query("SHOW TABLES", "optimize");
        while ($row = $this->db->get_array("optimize", MYSQL_NUM)) {
            if ($this->db->simple("OPTIMIZE TABLE {$row[0]}")) {
                 echo translate('GENERAL_DATABASE_OPTIMIZE_SUCCESS',$row[0]);
            } else {
                 echo translate('GENERAL_DATABASE_OPTIMIZE_FAIL',$row[0]);
            }
        }
    }

    function repair() {
        check_level(50, 'general', 'database', 'repair');
        if ($this->table_name == '')
            exit(translate('GENERAL_DATABASE_NO_TABLE_NAME'));
        if ($this->db->simple("REPAIR TABLE {$this->table_name}")) {
            echo translate('GENERAL_DATABASE_REPIRE_SUCCESS',$this->table_name);
        } else {
            echo translate('GENERAL_DATABASE_REPIRE_FAIL',$this->table_name);
        }
    }

    function repair_all() {
        check_level(50, 'general', 'database', 'repair');
        $this->db->query("SHOW TABLES", "repair");
        while ($row = $this->db->get_array('repair', MYSQL_NUM)) {
            if ($this->db->simple("REPAIR TABLE {$row[0]}")) {
                  echo translate('GENERAL_DATABASE_REPIRE_SUCCESS',$row[0]);
            } else {
                 echo translate('GENERAL_DATABASE_REPIRE_FAIL',$row[0]);
            }
        }
    }

    function backup() {
        check_level(50, 'general', 'database', 'repair');
        $tables = $this->get_tables();
        $filename = date("YmdHis") . ".sql";
        $backup_dir = DATAPATH . "database/";
        if (!is_dir($backup_dir)) {
            mkdir($backup_dir);
        }
        $fp = @fopen($backup_dir . $filename, 'w');
        foreach ($tables as $table => $item) {
            dump_table($table, $fp);
        }
        fclose($fp);
        echo translate('GENERAL_DATABASE_BACKUP_SUCCESS',$filename);
    }

    function query($sql = null) {
        $sqlquery = $sql ? $sql : stripslashes($_POST['sqlquery']);
        if ($sqlquery == '')
            exit( translate('GENERAL_DATABASE_QUERY_TIPS'));
        insert_log(translate('GENERAL_DATABASE_QUERY_LOG',$sqlquery));
        $sqlquery = str_replace("\r", "", $sqlquery);
        $sqls = preg_split("/;[ \t]{0,}\n/i", $sqlquery);
        $r = '';
        foreach ($sqls as $key => $val) {
            $r .= translate('GENERAL_DATABASE_QUERY_RUNNING',$val);
            //运行查询语句
            if (preg_match("/^(select|explain)(.*)/i ", $val)) {
                set_break("query");
                $this->db->safequery = false;
                $this->db->query($val);
                $list = $this->db->row();
                $usedseconds =translate('GENERAL_DATABASE_QUERY_SPENT_TIME',get_break("query"));
                if (count($list) <= 0) {
                    $r .= translate('GENERAL_DATABASE_NO_RETURN');
                } else {
                    $count = count($list);
                    $r .= translate('GENERAL_DATABASE_QUERY_RESULT',($count >= 1000 ? translate('GENERAL_DATABASE_OVER') : translate('GENERAL_DATABASE_OVER')), $count, ($count > 100 ? translate('GENERAL_DATABASE_MAX'): ''));
                }
                $r .= $usedseconds;
                $j = 0;
                foreach ($list as $m => $n) {
                    $j++;
                    if ($j > 100)
                        break;
                    $r .= "<hr style='height:1px;margin:5px 1px;background:#bbb;border:none;'/>";
                    $r .= "<span style='color:red'>". translate('GENERAL_DATABASE_NUMBER')."：$j</span><br />";
                    foreach ($n as $k => $v) {
                        $r .= "<font color='blue'>{$k}：</font>{$v}<br/>\r\n";
                    }
                }
            } else {
                set_break("query");
                $this->db->query($val);
                $usedseconds =translate('GENERAL_DATABASE_QUERY_SPENT_TIME',get_break("query"));
                $r .=translate('GENERAL_DATABASE_AFFECTEDS',$this->db->affected_rows(),$usedseconds);
            }
        }
        echo $r;
    }

}

//数据库辅助函数
//获取表的名称
function list_tables($database) {
    $rs = mysql_list_tables($database);
    $tables = array();
    while ($row = mysql_fetch_row($rs)) {
        $tables[] = $row[0];
    }
    mysql_free_result($rs);
    return $tables;
}

//导出数据库
function dump_table($table, $fp = null) {
    $need_close = false;
    if (is_null($fp)) {
        $fp = fopen($table . '.sql', 'w');
        $need_close = true;
    }
    $a = mysql_query("show create table `{$table}`");
    $row = mysql_fetch_assoc($a);
    fwrite($fp, $row['Create Table'] . ';'); //导出表结构
    $rs = mysql_query("SELECT * FROM `{$table}`");
    while ($row = mysql_fetch_row($rs)) {
        fwrite($fp, get_insert_sql($table, $row));
    }
    mysql_free_result($rs);
    if ($need_close) {
        fclose($fp);
    }
}

//导出表数据
function get_insert_sql($table, $row) {
    $sql = "INSERT INTO `{$table}` VALUES (";
    $values = array();
    foreach ($row as $value) {
        $values[] = "'" . mysql_real_escape_string($value) . "'";
    }
    $sql .= implode(', ', $values) . ");";
    return $sql;
}